package com.yuehuanghun.admin.generator.model.generator;

import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

import org.springframework.beans.factory.config.ConfigurableBeanFactory;
import org.springframework.context.annotation.Scope;
import org.springframework.stereotype.Component;

import com.github.pagehelper.Page;
import com.github.pagehelper.PageHelper;
import com.yuehuanghun.admin.generator.model.Table;
import com.yuehuanghun.framework.json.JSONObject;
import com.yuehuanghun.framework.util.DatabaseUtil;
import com.yuehuanghun.framework.util.StringUtils;

import cn.hutool.core.date.DatePattern;
import cn.hutool.core.date.DateUtil;

@Component
@Scope(scopeName = ConfigurableBeanFactory.SCOPE_PROTOTYPE)
public class SqlServerTableGenerator extends AbstractTableGenerator {

	@Override
	public String getDbName() {		
		return DatabaseUtil.DataBase.SQLSERVER.getDbName();
	}
	
	@Override
    public List<Table> getSimpleTableInfo(String tableName, String tableComment,
            Date createTimeBegin, Date createTimeEnd, int pageNum, int pageSize) {
        StringBuilder sql = new StringBuilder();
        sql.append(
                "SELECT obj.name tableName, ep.[value] tableComment, obj.crdate createTime from sysobjects obj ")
                .append(" LEFT JOIN sys.extended_properties ep ON obj.id = ep.major_id")
                .append(" WHERE obj.xtype='U' AND ep.minor_id = 0 AND ep.name = 'MS_Description'")
                .append(" AND obj.name NOT LIKE 'qrtz_%' AND obj.name NOT LIKE 'gen_%'")
                .append(" AND obj.name NOT IN (SELECT table_name FROM gen_table)");

        if (StringUtils.isNotBlank(tableName)) {
            sql.append(" AND obj.name LIKE '%").append(tableName).append("%'");
        }
        if (StringUtils.isNotBlank(tableComment)) {
            sql.append(" AND ep.[value] LIKE '%").append(tableComment).append("%'");
        }
        if (createTimeBegin != null) {
            sql.append(" AND obj.crdate >= '")
                    .append(DateUtil.format(createTimeBegin, DatePattern.NORM_DATETIME_FORMATTER))
                    .append("'");
        }
        if (createTimeEnd != null) {
            sql.append(" AND obj.crdate <= '")
                    .append(DateUtil.format(createTimeEnd, DatePattern.NORM_DATETIME_FORMATTER))
                    .append("'");
        }

        PageHelper.startPage(pageNum, pageSize);
        List<Map<String, Object>> mapList = tableMapper.getForMap(sql.toString());

        List<Table> tableList = mapList.stream().map(item -> {
            JSONObject json = new JSONObject(item);
            Table table = new Table();
            table.setJdbcName(json.getString("tableName"));
            table.setTableComments(json.getString("tableComment"));
            table.setCreateTime(json.getDate("createTime"));
            return table;
        }).collect(Collectors.toList());

        Page<Table> page = new Page<>();
        page.setTotal(((Page<?>) mapList).getTotal());
        page.addAll(tableList);
        return page;

    }

	@Override
	public Table genTable(String tableName) {
		Table genTable = super.genTable(tableName);
		List<Map<String, Object>> comments = getComments(tableName);
		if(!comments.isEmpty()) {
			genTable.setTableComments((String) comments.get(0).get("tableComment"));
			genTable.getColumns().forEach(column -> {
				comments.stream().filter(item -> column.getJdbcName().equals(item.get("columnName"))).findFirst().ifPresent(comm -> {
					column.setColumnComments((String) comm.get("columnComment"));
				});;
			});
		}
		
		return genTable;
	}

	//sqlserver的jdbc不返回表注释与列注释，所以通过sql查询一次
	private List<Map<String, Object>> getComments(String tableName){
		String sql = "SELECT CASE WHEN col.colorder = 1 THEN epTwo.[value] ELSE '' END AS tableComment,\r\n" + 
				" col.name AS columnName, ep.[value] AS columnComment\r\n" + 
				"FROM dbo.syscolumns col\r\n" + 
				"LEFT JOIN dbo.systypes t ON col.xtype = t.xusertype\r\n" + 
				"INNER JOIN dbo.sysobjects obj ON col.id = obj.id AND obj.xtype = 'U' AND obj.status >= 0\r\n" + 
				"LEFT JOIN sys.extended_properties ep ON col.id = ep.major_id AND col.colid = ep.minor_id AND ep.name = 'MS_Description'\r\n" + 
				"LEFT JOIN sys.extended_properties epTwo ON obj.id = epTwo.major_id\r\n" + 
				"AND epTwo.minor_id = 0\r\n" + 
				"AND epTwo.name = 'MS_Description'\r\n" + 
				"WHERE\r\n" + 
				"	obj.name = '" + tableName + "' \r\n" + 
				"ORDER BY\r\n" + 
				"	col.colorder" +
				"";
		
		return tableMapper.getForMap(sql);
	}
	
	private String getConnectedDatabase() {
		return jdbcTemplate.queryForObject("SELECT Name FROM Master..SysDataBases WHERE DbId=(SELECT Dbid FROM Master..SysProcesses WHERE Spid = @@spid)", String.class);
	}
	
	@Override
	public String getCatalog() {
		if(StringUtils.isBlank(catalog)) {
			this.catalog = getConnectedDatabase();
		}
		return catalog;
	}
}
